/**
 * 
 */
package vehicleService;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import constants.Constants;
import vehicleEntity.SaleVehicleEntity;

/**
 * Connect with DB, Execute SQL statements and Close Connection
 * @author ArimaRajan
 *
 */
public class SaleService {

	private Connection connection;
	private PreparedStatement statement;

	public SaleService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();//mouse shared
		} 
	}
	
	/**
	 * This method fetches the list of sale vehicles from SaleVehicles tables
	 * @param none
	 * @Pre variables are set in the SaleVehicle Entity
	 * @Post values are passed back to the SaleLogic
	 * @return result set of the executed select statement
	 * @throws SQLException 
	 */
	public ResultSet fetchSaleVehicle() throws SQLException{
		ResultSet rs = null;
		//displays the sale list
		String query = "select v.vehicleId, v.RegNo, v.VIN, v.vehicleType, v.vehicleClass, v.VehicleName, sv.movedOn, sv.saleAmount from vehicle v join sale_vehicle sv where v.vehicleId=sv.saleVehicleId and soldAmount is null order by v.vehicleType";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e1;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

		return rs;

	}

/**
 * This method is used to close the SQL Connection
 */
	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}
	
	/**
	 * This method fetches the details of the vehicle with given vehicle ID from Sale vehicle table
	 *
	 * @param VehicleId
	 * @Pre VehicleId is retrieved from SaleLogic
	 * @Post Values are passed back to the SaleLogic
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchSaleVehicleDetail_for_edit(int VehicleId) throws SQLException{
		System.out.println("Scene3--> Sale Logic --> Sale Service --> In sale service : "+VehicleId);
		ResultSet rs = null;
		//displays the search value from the sale list
		String query = "select v.vehicleId, v.RegNo, v.VIN, v.vehicleType, v.vehicleClass, v.VehicleName, sv.movedOn, sv.saleAmount from vehicle v join sale_vehicle sv where v.vehicleId=sv.saleVehicleId and soldAmount is null and vehicleId=? order by v.vehicleType";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			System.out.println("Vehicle Id : " + VehicleId);
			statement.setInt(1, VehicleId);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

		return rs;
	}

	/**
	 * This method saves the updated value of the Sale vehicle in Sale table
	 * @param purchasedVehicle
	 * @Pre saleVehicle value is retrieved from SaleVehicle Entity
	 * @Post Using saleVehicle value the values are updated in the database by the SQL statement
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet saveUpdated_for_query1(SaleVehicleEntity saleVehicle) throws SQLException{
		ResultSet rs = null;
		//updates the vehicle table
		String updatequery1 = "update vehicle set RegNo = ? , VIN = ? , vehicleType = ? , vehicleClass = ? , VehicleName = ? where vehicleId = ? ;";
		System.out.println("in saleservice : "+saleVehicle.getVehicleClass());
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery1);
			statement.setString(1, saleVehicle.getRegNo());
			statement.setString(2, saleVehicle.getVin());
			statement.setString(3, saleVehicle.getVehicleType());
			statement.setString(4, saleVehicle.getVehicleClass());
			statement.setString(5, saleVehicle.getVehicleName());
			statement.setInt(6, saleVehicle.getVehicleId());
			/* statement.setDate(7, new Date(saleVehicle.getMovedOn().getTime()));
			statement.setDouble(8, saleVehicle.getSaleAm
			ount());
			statement.setInt(9, saleVehicle.getVehicleId()); */
			statement.executeUpdate();
			//connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method is used to commit the updates done 
	 * @throws SQLException
	 */
	public void transactionCommit() throws SQLException{
		if(connection!=null){
			try {
				connection.commit();
			} catch (SQLException e) {
				if(connection != null){
					System.out.println("transaction is rolled back");
					try {
						connection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
						throw e1;
					}
				}
				e.printStackTrace();
				throw e;
			}
			finally{
				try {
					connection.setAutoCommit(true);
				}
				catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
	}
	
	/**
	 * This method is used to roll back the transaction
	 * @throws SQLException
	 */

	public void transactionRollBack() throws SQLException{

		try {
			if(connection != null){
				connection.rollback();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}

		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
	}
	
	/**
	 * This method is used to update the table called sale_vehicle
	 * @param saleVehicle
	 * @Pre saleVehicle values are retrieved from the SaleVehicleEntity class
	 * @Post Updated values are reflected in the database
	 * @return
	 * @throws SQLException 
	 */

	public ResultSet saveUpdated_for_query2(SaleVehicleEntity saleVehicle) throws SQLException{
		ResultSet rs = null;
		//updates the vehicle table
		String updatequery1 = "update sale_vehicle set saleAmount = ?  where saleVehicleId = ? ;";
		System.out.println("in saleservice : "+saleVehicle.getVehicleClass());
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery1);
			statement.setDouble(1, saleVehicle.getSaleAmount());
			statement.setInt(2, saleVehicle.getVehicleId());

			statement.executeUpdate();
			//connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method is used to fethc the details from sale_vehicle and vehicle tables
	 * @param VehicleId
	 * @Pre VehicleId value is retrieved from SaleLogic class
	 * @Post The selected values are then passed back to the SaleLogic class
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchSaleVehicleDetail_for_sale(int VehicleId) throws SQLException {
		System.out.println("Scene6--> Sale Logic --> Sale Service --> In sale service : "+VehicleId);
		ResultSet rs = null;
		//displays the search value from the sale list
		String query = "select v.VehicleName, v.vehicleClass,v.vehicleType,  sv.saleAmount, sv.soldAmount, sv.soldTo, sv.soldDate from vehicle v join sale_vehicle sv where v.vehicleId=sv.saleVehicleId and vehicleId=? and soldAmount is NULL; ";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			System.out.println("Vehicle Id : " + VehicleId);
			statement.setInt(1, VehicleId);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

		return rs;
	}
    
	/**
	 * This method is used to update the values of sale_vehicle table
	 * @param saleVehicle
	 * @Pre saleVehicle value is retrieved from saleVehicleEntity
	 * @Post  values are passed back to the SaleVehicleEntity class
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet saveUpdated_for_sell(SaleVehicleEntity saleVehicle) throws SQLException{
		ResultSet rs = null;
		//updates the vehicle table
		String updatequery1 = "update sale_vehicle set soldAmount = ? , soldTo = ? , soldDate = ? where saleVehicleId = ? ;";
		System.out.println("in saleservice : "+saleVehicle.getVehicleClass());
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery1);
			statement.setDouble(1, saleVehicle.getSoldAmount());
			statement.setString(2, saleVehicle.getSoldTo());
			statement.setDate(3, new Date(saleVehicle.getSoldDate().getTime())); // for sold date getTime is used
			statement.setInt(4, saleVehicle.getVehicleId());
			statement.executeUpdate();
			//connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

}
